The Data Object Wizard

The Data Object Wizard is an add-in that can help you to quickly generate data-aware class and UserControl modules. This wizard is probably the most sophisticated add-in provided with Visual Basic 6. Unfortunately, it's also one of the least intuitive to use. In the remainder of this chapter, I'll briefly introduce this utility. (Space doesn't permit me to explain all its capabilities in depth.)

The Data Object Wizard works in conjunction with the DataEnvironment designer. Instead of entering all the necessary information about the data source when the Data Object Wizard is executing, you have to prepare a DataEnvironment object with one or more Command objects before running the add-in. Each Command object represents one of the actions that you can perform on the data source: select, insert, update, or delete records, lookup values, and so on. Once you run the wizard, you can't go back to the Visual Basic IDE, so you need to prepare all the Command objects in advance.

In this section, I guide you through a simple example based on the Products table of the NWind.mdb database. Frankly, the Data Object Wizard works best when used on SQL Server and Oracle databases. Even so, I opted for an example based on a local MDB database for those of you who don't have a client/server system available. These are the preparatory steps that you have to follow before running the wizard:

1. Open the DataView window, and create a data link to the NWind.mdb database (if you don't have one already). Select the OLE DB Provider for ODBC, not the Provider for Microsoft Jet databases, if you want to be in sync with the following description. Test that the connection is working, and then expand the Tables subfolder under the NWind data link you just created.
2. Click the Add A DataEnvironment button in the DataView window to create a new DataEnvironment designer, and then delete the default Connection1 node that Visual Basic automatically adds to all DataEnvironment modules.
3. Drag the Products table from the DataView window into the DataEnvironment window. Visual Basic automatically creates a new Connection1 node and a Command object beneath it, named Products. The Recordset returned by this Command object doesn't necessarily have to be updatable because all the insert, update, and delete operations are performed by means of other Command objects.
4. Click on the Add Command button on the DataEnvironment toolbar to create a new Command object named Command1, and then click on the Properties button to show its Properties dialog box. Change the name of the Command object to Products_Insert, select the SQL Statement option, and enter the following SQL query string into the multiline text box under it, as shown in Figure 18-6:

INSERT INTO Products(ProductName, CategoryID, SupplierID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)



Figure 18-6. The Properties dialog box of the Products_Insert Command object.

5. Switch to the Parameters tab of the Properties dialog box, and assign a meaningful name to the nine parameters of the preceding SQL command. Each parameter should be assigned the name of the corresponding field—that is, ProductName, CategoryID, and so on. The majority of parameters are Long integers, so in most cases you don't need to modify the default settings. The exceptions are two string parameters (ProductName and QuantityPerUnit), one Currency parameter (UnitPrice), and one Boolean parameter (Discontinued). Set a greater-than-zero value for the Size attribute of string parameters; if you don't, the Command object won't be created correctly.
6. Create another Command object, assign it the name Products_Update, and enter the following string in the SQL Statement field:

UPDATE Products SET ProductName = ?, CategoryID = ?, SupplierID = ?, QuantityPerUnit = ?, UnitPrice = ?, UnitsInStock = ?, UnitsOnOrder = ?, ReorderLevel = ?, Discontinued = ? WHERE (ProductID = ?)

Then switch to the Parameters tab and assign meaningful names and types for all the parameters, exactly as you did in step 5. (Unfortunately, there's no way to copy and paste this information between Command objects.)

7. Create a fourth Command object, name it Products_Delete, and enter the following string in the SQL Statement field:

DELETE FROM Products WHERE ProductID = ?

Then switch to the Parameters tab and assign the ProductID name to the only parameter, without changing the other attributes.

8. Drag the Categories and Suppliers tables from the DataView window to the DataEnvironment window. This creates two Command objects, named Categories and Suppliers, that the Data Object Wizard will use to create lookup tables for the CategoryID and SupplierID fields, respectively.

All these steps shouldn't take more than 5 or 10 minutes. If you prefer, you can start with a blank Standard EXE project and then just load the DE1.dsr file from the project on the companion CD. This file already contains all the Command objects ready to be used by the Data Object Wizard.

Most of the time required to build these Command objects is spent manually entering the names and other attributes of the many parameters in the Products_Insert and Products_Update commands. You can't avoid this step when working with MDB databases because the OLE DB provider doesn't correctly recognize any parameterized QueryDef object stored in the database. The good news is that when you work with SQL Server you can create Command objects that link to stored procedures. In this case, the DataEnvironment designer is able to deduce the name and type of parameters without your help, which dramatically reduces the amount of time necessary to complete these preparatory steps.

You're now ready to run the Data Object Wizard. If you haven't loaded it yet, select the Add-In Manager command from the Add-Ins menu, double-click on its name in the list of available add-ins, and click on the OK button. Now the wizard should be available in the Add-Ins menu. Run it, and then follow these steps:

1. Click on the Next button to get past the introduction page; in the Create Object page, select the kind of object you want to create. The Data Object Wizard can create data consumer classes that bind to a data source or UserControl modules that bind to a data consumer class (which you must have created previously with the wizard). The first time you run the wizard, you have no choice; you must select the first option, A Class Object To Which Other Objects Can Bind Data. Click Next to advance to the next page.
2. In the Select Data Environment Command page, you select the source Command object—namely, the command that should be used by the class to fetch data. In this example, you select the Products command, which retrieves data directly from a database table; in real-world applications, you'll probably select a command that reads data using a stored procedure or an SQL SELECT query.
3. In the Define Class Field Information page, you indicate which fields are the primary keys in the Recordset and which fields can't be Null (required values). In this particular example, the ProductID field is the primary key, and the ProductName, SupplierID, and CategoryID fields can't be Null.
4. In the Define Lookup Table Information page, you define the lookup fields in the source command. As you know, a lookup field is a field whose value is used as a key into another table to retrieve data. For example, you can display the CompanyName field from the Suppliers table instead of the SupplierID field. In this case, you define SupplierID as a lookup field into the Suppliers table. To let the wizard generate the correct code, you must enter the following data into the page:

• Select SupplierID as the Source field (that is, the lookup field in the source command).
• Select Suppliers as the Lookup command. (This tells the wizard which Command object should be used to map the lookup value onto a more readable string to be displayed to the user.)
• Select CompanyName in the Display Field combo box. (This is the field in the Lookup Command object that provides the decoded value.)
• Tick the SupplierID entry in the Lookup On Field(s) list box. (This is the name of the lookup field as it appears in the Lookup command; it might or might not be the same as the name of the field in the source Command.) Click on the Add button to proceed.

Repeat the same four operations to define CategoryID as another lookup field, as shown in Figure 18-7.



Figure 18-7. The Define Lookup Table Information page, after you've added the SupplierID field to the list of lookup fields and just before you add the CategoryID field.

5. In the next page, Map Lookup Fields, you define how the fields in the source Command object map onto the fields in the Lookup Command object; there are two consecutive pages for this purpose because you defined two Lookup Command objects. Because the field names are the same in the two Command objects, the wizard can do the mapping correctly all by itself, so you can click on the Next button without modifying the values in the grid.
6. In the Define And Map Insert Data Command page, you select which DataEnvironment Command, if any, should be used for adding new records to the source Recordset. In this example, you should select the Products_Insert Command object. You can then define the mapping between the fields in the source Command and the parameters in the Insert Command. Because we picked parameters' names that are the same as the fields' names, the wizard is able to do the correct mapping by itself, and you don't have to modify the proposed mapping scheme. (See Figure 18-8.)



Figure 18-8. Mapping field names to parameter names is crucial when you define Command objects for inserting, updating, and deleting records, but in most cases the wizard is able to do the mapping automatically.

7. In the next page, Define And Map Update Data Command, you select the Command object that should be used for updating the source Command (the Products_Update Command in this example). You can also tick the Use Insert Command For Update check box when you have a stored procedure that can both append a new record and update an existing one. Again, in this example, the wizard is able to correctly map field names to parameter names, so you can skip to the next page.
8. In the Define And Map Delete Data Command page, you select which Command object should be used to delete a record from the source Command (the Products_Delete Command in this example). Again, you don't need to manually map fields; the wizard does the mapping automatically.
9. You're about to complete running the wizard. In the last page, enter a name for the class and click on the Finish button. The class name you provide is automatically prefixed with the rscls characters. For example, if you enter Products, the wizard will create the rsclsProducts class module.

Going through the preceding steps might seem complex at first, but after some practice you'll see that using the wizard really takes no more than a couple of minutes. When the wizard completes its execution, you'll find that two new classes have been added to the current project: the clsDow class and the rsclsProducts class. The clsDow class module contains only the EnumSaveMode enumerated constants, which define the values that can be assigned to the SaveMode property of the rsclsProducts class: 0-adImmediate, if you want the class to save values in the source Recordset as soon as the record pointer moves to another record, or 1-adBatch, if the class should update the Recordset only when you invoke the class's Update method.

You could use the rsclsProducts class module created by the wizard directly from your applications, but you'll find it more convenient to use it through a custom UserControl. The great news is that you can create such a UserControl in a matter of seconds, again using the Data Object Wizard.

1. Run the wizard again, and in its Create Object page, select the A UserControl Object Bound To An Existing Class Object option.
2. In the next page, select the data class to use as the data source for the UserControl (the rsclsProducts class in this example).
3. In the Select User Control Type page, decide which type of UserControl you want to create. The wizard lets you choose among Single Record (a collection of individual fields), Data Grid (a DataGrid-like control), ListBox, and ComboBox. For this first run, select the Single Record option.
4. In the next page, decide which database fields are visible in the UserControl and which control type is to be used for each visible field. For example, you should select (None) for the ProductID field because this is an auto-increment primary key field that's meaningless to the user, and you should use a ComboBox field for the CategoryName and SupplierName lookup fields (as shown in Figure 18-9).
5. In the next page, you select a base name for the control. In most cases, you can accept the default name (Products in this example) and click on the Finish button. The actual name the wizard uses depends on which type of UserControl you selected in step 3. For example, if you selected a Single Record type of control, the UserControl module that the wizard generates is named uctProductsSingleRecord.



Figure 18-9. The Map Class Properties To A Control Type page of the DataObject Wizard.

You're now ready to use the control in the application. Close the UserControl module so that its icon in the Toolbox becomes active, create an instance of the control on a form, and add a few navigational buttons, as shown in Figure 18-10. The code behind these buttons is really simple:

Private Sub cmdPrevious_Click() uctProductsSingleRecord1.MovePrevious End Sub Private Sub cmdNext_Click() uctProductsSingleRecord1.MoveNext End Sub Private Sub cmdAddNew_Click() uctProductsSingleRecord1.AddRecord End Sub Private Sub cmdUpdate_Click() uctProductsSingleRecord1.Update End Sub Private Sub cmdDelete_Click() uctProductsSingleRecord1.Delete End Sub

Figure 18-10. The ActiveX control generated by the Data Object Wizard can be tested with a few navigational buttons on its parent form.

The Data Object Wizard isn't particularly efficient when used with the OLE DB Provider for Microsoft Jet. After some experiments, I found that if you want to add new records, you must set the UserControl's SaveMode property to 1-adBatch, and therefore you have to invoke the Update method after entering a new record. Everything works unproblematically when you create classes and UserControls that bind to a SQL Server database.

When you understand the mechanism, creating the other types of UserControls is easy. For example, restart the wizard and create a DataGrid-like control. If you then place the control on a form and set its GridEditable property to True, you'll see that not only can you edit field values in the grid but you can also select the value of a lookup field from a drop-down list, as shown in Figure 18-11. The DataList-like and DataCombo-like controls are even simpler because they're just lists of values and don't use the Insert, Update, Delete, and Lookup Command objects.

You can create more flexible classes and controls if the original source Command object is based on a parameterized query or a stored procedure, such as this one:

SELECT * FROM Products WHERE ProductName LIKE ?

In this case, the resulting class and UserControl modules expose a property whose name is obtained by concatenating the name of the source Command and the name of the parameter in the query (for example, Products_ProductName). You can set this property at design time and let the UserControl initialize the internal Recordset as soon as the control is created at run time. Or you can set the ManualInitialize property to True so that you can assign this property using code and then manually invoke the Initxxxx method exposed by the control (InitProducts in this example). The sample application shown in Figure 18-11 uses this technique to narrow the number of records displayed in the grid. This is the only code in the form module:

Private Sub cmdFetch_Click() uctProductsDataGrid1.Products_FetchProductName = txtProductName & "%" uctProductsDataGrid1.InitProducts2 End Sub

The Data Object Wizard is a great add-in, and it produces very good code. In fact, I suggest that you study the generated code to learn how to get the maximum benefit from this utility and also to learn new tricks for building better data-aware classes and UserControls. The wizard also has some defects, however. Apart from those that I've already mentioned (and that are mostly caused by bugs in the OLE DB Provider for Microsoft Jet), the one that bothers me most is that the UserControl module tends to go out of sync with its instances on forms, so you often need to right-click on forms and invoke the Update UserControls menu command. This is a minor nuisance, however, when compared to the time the wizard saves.

Figure 18-11. DataGrid-like UserControls also let you select values from drop-down lists.

In this chapter, you've seen that ADO permits you to build many new types of classes and components: data consumers, data sources, and OLE DB Simple Providers. You can build another type of database component with Visual Basic, a Remote Data Services (RDS) component. You normally use these when you're accessing a database through the HTTP protocol, and for this reason I'll describe this type of component in the next chapter, together with the new Visual Basic features in the Internet area.